// jTDS JDBC Driver for Microsoft SQL Server and Sybase
// Copyright (C) 2004 The jTDS Project
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
package net.sourceforge.jtds.jdbc;
import junit.framework.Test;
import junit.framework.TestSuite;
import java.sql.*;
import java.math.BigDecimal;
/**
* Test case to illustrate errors reported by SUN JBDC compatibility test suite.
*
* @version 1.0
*/
public class SunTest extends DatabaseTestCase {
public static Test suite() {
return new TestSuite(SunTest.class);
}
public SunTest(String name) {
super(name);
}
/**
* Test for SUN bug [ PrepStmt1.getMetaData() ]
* Driver loops if select contains commas.
*
* @throws Exception
*/
public void testGetMetaData() throws Exception {
PreparedStatement pstmt = con.prepareStatement("SELECT name, id, type FROM sysobjects WHERE type = 'U'");
ResultSetMetaData rsmd = pstmt.getMetaData();
assertEquals("name", rsmd.getColumnName(1));
pstmt.close();
}
/**
* Generic Tests for SUN bugs such as
* <ol>
* <li>Can't convert VARCHAR to Timestamp
* <li>Can't convert VARCHAR to Time
* <li>Can't convert VARCHAR to Date
* <li>Internal time representation causes equals to fail
* </ol>
*
* @throws Exception
*/
public void testDateTime()
throws Exception
{
dropProcedure( "CTOT_PROC" );
dropTable( "CTOT" );
final String dateStr = "1983-01-31";
final String timeStr = "12:59:59";
final String tsStr = "1983-01-31 23:59:59.333";
Statement stmt = con.createStatement();
stmt.execute( "CREATE TABLE CTOT (tdate DATETIME, ttime DATETIME, tts DATETIME, tnull DATETIME NULL)" );
stmt.execute( "CREATE PROC CTOT_PROC @tdate DATETIME OUTPUT, @ttime DATETIME OUTPUT, @tts DATETIME OUTPUT AS " + "BEGIN SELECT @tdate=tdate, @ttime=ttime, @tts=tts FROM CTOT END" );
stmt.close();
PreparedStatement pstmt = con.prepareStatement( "INSERT INTO CTOT (tdate, ttime, tts) VALUES(?,?,?)" );
pstmt.setObject( 1, dateStr, java.sql.Types.DATE );
pstmt.setObject( 2, timeStr, java.sql.Types.TIME );
pstmt.setObject( 3, tsStr, java.sql.Types.TIMESTAMP );
pstmt.execute();
assertEquals( 1, pstmt.getUpdateCount() );
pstmt.close();
CallableStatement cstmt = con.prepareCall( "{call CTOT_PROC(?,?,?)}" );
cstmt.registerOutParameter( 1, java.sql.Types.DATE );
cstmt.registerOutParameter( 2, java.sql.Types.TIME );
cstmt.registerOutParameter( 3, java.sql.Types.TIMESTAMP );
cstmt.execute();
assertEquals( dateStr, cstmt.getString( 1 ) );
assertEquals( timeStr, cstmt.getString( 2 ) );
assertEquals( java.sql.Time.valueOf( timeStr ), cstmt.getTime( 2 ) );
assertEquals( tsStr, cstmt.getString( 3 ) );
cstmt.close();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM CTOT" );
assertTrue( rs.next() );
java.sql.Time retval = rs.getTime( 2 );
java.sql.Time tstval = java.sql.Time.valueOf( timeStr );
assertEquals( tstval, retval );
stmt.close();
pstmt = con.prepareStatement( "UPDATE CTOT SET tnull = ?" );
pstmt.setTime( 1, tstval );
pstmt.execute();
assertEquals( 1, pstmt.getUpdateCount() );
pstmt.close();
stmt = con.createStatement();
rs = stmt.executeQuery( "SELECT * FROM CTOT" );
assertTrue( rs.next() );
retval = rs.getTime( 4 );
assertEquals( tstval, retval );
stmt.close();
}
/**
* Generic test for errors caused by promotion out parameters of Float to
* Double by driver. eg [ callStmt4.testGetObject34 ] Class cast exception
* Float.
*
* @throws Exception
*/
public void testCharToReal()
throws Exception
{
dropProcedure( "CTOR_PROC" );
dropTable( "CTOR" );
final String minStr = "3.4E38";
final String maxStr = "1.18E-38";
Statement stmt = con.createStatement();
stmt.execute( "CREATE TABLE CTOR (min_val REAL, max_val REAL)" );
stmt.execute( "CREATE PROC CTOR_PROC @minval REAL OUTPUT, @maxval REAL OUTPUT AS BEGIN SELECT @minval=min_val, @maxval=max_val FROM CTOR END" );
stmt.execute( "INSERT INTO CTOR VALUES(" + minStr + "," + maxStr + ")" );
assertEquals( 1, stmt.getUpdateCount() );
ResultSet rs = stmt.executeQuery( "SELECT * FROM CTOR" );
assertNotNull( rs );
assertTrue( rs.next() );
assertEquals( minStr, rs.getString( 1 ) );
assertEquals( maxStr, rs.getString( 2 ) );
assertTrue( rs.getObject( 1 ) instanceof Float );
stmt.close();
CallableStatement cstmt = con.prepareCall( "{call CTOR_PROC(?,?)}" );
cstmt.registerOutParameter( 1, java.sql.Types.REAL );
cstmt.registerOutParameter( 2, java.sql.Types.REAL );
cstmt.execute();
assertEquals( minStr, cstmt.getString( 1 ) );
assertEquals( maxStr, cstmt.getString( 2 ) );
cstmt.close();
}
/**
* Generic test for SUN bugs: bigint null parameter values sent as integer
* size.
*
* @throws Exception
*/
public void testCharToLong()
throws Exception
{
dropProcedure( "CTOL_PROC" );
dropTable( "CTOL" );
final String minStr = "9223372036854775807";
final String maxStr = "-9223372036854775808";
Statement stmt = con.createStatement();
stmt.execute( "CREATE TABLE CTOL (min_val BIGINT, max_val BIGINT)" );
stmt.execute( "CREATE PROC CTOL_PROC @minval BIGINT OUTPUT, @maxval BIGINT OUTPUT AS BEGIN SELECT @minval=min_val, @maxval=max_val FROM CTOL END" );
stmt.execute( "INSERT INTO CTOL VALUES(" + minStr + "," + maxStr + ")" );
assertEquals( 1, stmt.getUpdateCount() );
ResultSet rs = stmt.executeQuery( "SELECT * FROM CTOL" );
assertNotNull( rs );
assertTrue( rs.next() );
assertEquals( minStr, rs.getString( 1 ) );
assertEquals( maxStr, rs.getString( 2 ) );
stmt.close();
CallableStatement cstmt = con.prepareCall( "{call CTOL_PROC(?,?)}" );
cstmt.registerOutParameter( 1, java.sql.Types.BIGINT );
cstmt.registerOutParameter( 2, java.sql.Types.BIGINT );
cstmt.execute();
assertEquals( minStr, cstmt.getString( 1 ) );
assertEquals( maxStr, cstmt.getString( 2 ) );
cstmt.close();
}
/**
* Test for SUN bug [ dbMeta8.testGetProcedures ]
* The wrong column names are returned by getProcedures().
*
* @throws Exception
*/
public void testGetProcedures() throws Exception {
String names[] = {"PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME","","","","REMARKS","PROCEDURE_TYPE"};
DatabaseMetaData dbmd = con.getMetaData();
ResultSet rs = dbmd.getProcedures(null, null, "%");
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 0; i < names.length; i++) {
if (names[i].length() > 0) {
assertEquals(names[i], rsmd.getColumnName(i+1));
}
}
rs.close();
}
/**
* Generic test for SUN bug where Float was promoted to Double
* by driver leading to ClassCastExceptions in the tests.
* Example [ prepStmt4.testSetObject16 ]
*
* @throws Exception
*/
public void testGetFloatObject() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #GETF (val REAL)");
stmt.execute("INSERT INTO #GETF (val) VALUES (1.7E10)");
assertEquals(1,stmt.getUpdateCount());
ResultSet rs = stmt.executeQuery("SELECT * FROM #GETF");
assertTrue(rs.next());
assertTrue(rs.getObject(1) instanceof Float);
rs.close();
stmt.close();
}
/**
* Test for SUN bug [ resultSet1.testSetFetchSize02 ]
* attempt to set non zero fetch size rejected.
*
* @throws Exception
*/
public void testSetFetchSize() throws Exception {
CallableStatement cstmt = con.prepareCall("{call sp_who}");
ResultSet rs = cstmt.executeQuery();
rs.setFetchSize(5);
assertEquals(5, rs.getFetchSize());
rs.close();
cstmt.close();
}
/**
* Test for SUN bug [ stmt2.testSetFetchDirection04 ]
* fetch direction constant not validated.
*
* @throws Exception
*/
public void testSetFetchDirectiion() throws Exception {
Statement stmt = con.createStatement();
try {
stmt.setFetchDirection(-1);
fail("setFecthDirection does not validate parameter");
} catch (SQLException sqe) {
}
stmt.close();
}
/**
* Test for bug [ 1012307 ] PreparedStatement.setObject(java.util.Date) not working.
* The driver should throw an exception if the object is not of a valid
* type according to table
*
* @throws Exception
*/
public void testSetDateObject() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #SETD (val DATETIME)");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #SETD (val) VALUES (?)");
long tval = 60907507200000L; //1999-12-31
try {
pstmt.setObject(1, new java.util.Date(tval));
fail("No exception for setObject(java.util.Date)");
} catch (SQLException e) {
// OK unsupported object type trapped
}
pstmt.close();
stmt.close();
}
/**
* Test for bug [ 1012301 ] 0.9-rc1: Prepared statement execution error.
*
* @throws Exception
*/
public void testPrepStmtError() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #PERR (val VARCHAR(255))\r\n" +
"INSERT INTO #PERR (val) VALUES('Test String')");
PreparedStatement pstmt = con.prepareStatement(" SELECT * FROM #PERR WHERE val = ?");
pstmt.setString(1,"Test String");
assertTrue(pstmt.execute());
ResultSet rs = pstmt.getResultSet();
assertTrue(rs.next());
rs.close();
pstmt.close();
stmt.close();
}
/**
* Test for bug [ 1011650 ] 0.9-rc1: comments get parsed
*
* @throws Exception
*/
public void testSqlComments() throws Exception {
String testSql = "/* This is a test of the comment {fn test()} parser */\r\n" +
"SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+
"INSERT INTO B VALUES({d 1999-01-01}) -- Unterminated in line comment";
String outSql = "/* This is a test of the comment {fn test()} parser */\r\n" +
"SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+
"INSERT INTO B VALUES(convert(datetime,'19990101')) -- Unterminated in line comment";
assertEquals(outSql, con.nativeSQL(testSql));
}
/**
* Test for bug [ 1008126 ] Metadata getTimeDateFunctions() wrong
*
* @throws Exception
*/
public void testDateTimeFn() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #DTFN (ttime SMALLDATETIME, tdate SMALLDATETIME, ftime SMALLDATETIME, fdate SMALLDATETIME, tnow DATETIME)");
stmt.execute("INSERT INTO #DTFN (ttime, tdate, ftime, fdate, tnow) VALUES (getdate(), getdate(), {fn curtime()}, {fn curdate()}, {fn now()})");
assertEquals(1, stmt.getUpdateCount());
ResultSet rs = stmt.executeQuery("SELECT * FROM #DTFN");
assertTrue(rs.next());
assertEquals("curdate()",rs.getDate(2),rs.getDate(4));
assertEquals("curtime()",rs.getTime(1),rs.getTime(3));
assertEquals("now()",rs.getDate(1),rs.getDate(5));
rs = stmt.executeQuery("SELECT {fn dayname('2004-08-21')}, " +
"{fn dayofmonth('2004-08-21')}, " +
"{fn dayofweek('2004-08-21')}," +
"{fn dayofyear('2004-08-21')}," +
"{fn hour('23:47:32')}," +
"{fn minute('23:47:32')}," +
"{fn second('23:47:32')}," +
"{fn year('2004-08-21')}," +
"{fn quarter('2004-08-21')}," +
"{fn month('2004-08-21')}," +
"{fn week('2004-08-21')}," +
"{fn monthname('2004-08-21')}," +
"{fn timestampdiff(SQL_TSI_DAY, '2004-08-19','2004-08-21')}," +
"{fn timestampadd(SQL_TSI_MONTH, 1, '2004-08-21')}" +
"");
assertTrue(rs.next());
assertEquals("dayname", "Saturday", rs.getString(1));
assertEquals("dayofmonth", 21, rs.getInt(2));
assertEquals("dayofweek", 7, rs.getInt(3));
assertEquals("dayofyear", 234, rs.getInt(4));
assertEquals("hour", 23, rs.getInt(5));
assertEquals("minute", 47, rs.getInt(6));
assertEquals("second", 32, rs.getInt(7));
assertEquals("year", 2004, rs.getInt(8));
assertEquals("quarter", 3, rs.getInt(9));
assertEquals("month", 8, rs.getInt(10));
assertEquals("week", 34, rs.getInt(11));
assertEquals("monthname", "August", rs.getString(12));
assertEquals("timestampdiff", 2, rs.getInt(13));
assertEquals("timestampadd", java.sql.Date.valueOf("2004-09-21"), rs.getDate(14));
stmt.close();
}
/**
* Test for scalar string functions.
*
* @throws Exception
*/
public void testStringFn() throws Exception {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT {fn ascii('X')}, "+
"{fn char(88)}," +
"{fn concat('X','B')}," +
"{fn difference('X','B')}," +
"{fn insert('XXX',2,1, 'Y')}," +
"{fn lcase('XXX')}," +
"{fn length('XXX')}," +
"{fn ltrim(' XXX')}," +
"{fn repeat('X', 3)}," +
"{fn replace('XXXYYYXXX', 'YYY', 'FRED')}," +
"{fn right('XXX', 1)}," +
"{fn rtrim('XXX ')}, " +
"{fn soundex('FRED')}," +
"'X' + {fn space(1)} + 'X'," +
"{fn substring('FRED', 2, 1)}," +
"{fn ucase('xxx')}," +
"{fn locate('fred', 'xxxfredyyy')}," +
"{fn left('FRED', 1)}" +
"");
assertTrue(rs.next());
assertEquals("ascii", 88, rs.getInt(1));
assertEquals("char", "X", rs.getString(2));
assertEquals("concat", "XB", rs.getString(3));
assertEquals("difference", 3, rs.getInt(4));
assertEquals("insert", "XYX", rs.getString(5));
assertEquals("lcase", "xxx", rs.getString(6));
assertEquals("insert", 3, rs.getInt(7));
assertEquals("ltrim", "XXX", rs.getString(8));
assertEquals("repeat", "XXX", rs.getString(9));
assertEquals("replace", "XXXFREDXXX", rs.getString(10));
assertEquals("right", "X", rs.getString(11));
assertEquals("rtrim", "XXX", rs.getString(12));
assertEquals("soundex", "F630", rs.getString(13));
assertEquals("space", "X X", rs.getString(14));
assertEquals("substring", "R", rs.getString(15));
assertEquals("ucase", "XXX", rs.getString(16));
assertEquals("locate", 4, rs.getInt(17));
assertEquals("left", "F", rs.getString(18));
stmt.close();
}
/**
* Test nested escapes
*
* @throws Exception
*/
public void testNestedEscapes() throws Exception {
String sql = "SELECT {fn convert({fn month({fn now()})},varchar)} WHERE X";
assertEquals("SELECT convert(varchar,datepart(month,getdate())) WHERE X", con.nativeSQL(sql));
sql = "{?=call testproc(?, {fn now()})}";
assertEquals("EXECUTE testproc ?,getdate()", con.nativeSQL(sql));
sql = "SELECT * FROM {oj t1 LEFT OUTER JOIN {oj t2 LEFT OUTER JOIN t2 ON condition1} ON condition2}";
assertEquals("SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t2 ON condition1 ON condition2", con.nativeSQL(sql));
}
/**
* Test conversion of various types to LONGVARCHAR. This functionality was
* broken in 0.9 because changes were made to handle LONGVARCHAR internally
* as Clob rather than String (but these did not take into consideration
* all possible cases.
*
* @throws SQLException
*/
public void testConversionToLongvarchar() throws SQLException {
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE #testConversionToLongvarchar ("
+ " id INT,"
+ " val " + ( isMSSQL() ? "NTEXT" : "UNITEXT" ) + ")");
int id = 0;
String decimalValue = "1234.5678";
String booleanValue = "true";
String integerValue = "1234567";
String longValue = "1234567890123";
Date dateValue = new Date(System.currentTimeMillis());
Time timeValue = new Time(System.currentTimeMillis());
Timestamp timestampValue = new Timestamp(System.currentTimeMillis());
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO #testConversionToLongvarchar (id, val) VALUES (?, ?)");
// Test BigDecimal to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new BigDecimal(decimalValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Boolean to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new Boolean(booleanValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Integer to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new Integer(integerValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Long to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new Long(longValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Float to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new Float(integerValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Double to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, new Double(longValue), java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Date to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, dateValue, java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Time to LONGVARCHAR conversion
pstmt.setInt(1, ++id);
pstmt.setObject(2, timeValue, java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
// Test Timestamp to LONGVARCHAR conversion
pstmt.setInt(1, id);
pstmt.setObject(2, timestampValue, java.sql.Types.LONGVARCHAR);
pstmt.executeUpdate();
pstmt.close();
ResultSet rs = stmt.executeQuery(
"SELECT * FROM #testConversionToLongvarchar ORDER BY id");
assertTrue(rs.next());
assertEquals(decimalValue, rs.getString("val"));
assertTrue(rs.next());
assertEquals("1", rs.getString("val"));
assertTrue(rs.next());
assertEquals(integerValue, rs.getString("val"));
assertTrue(rs.next());
assertEquals(longValue, rs.getString("val"));
assertTrue(rs.next());
assertEquals(Float.parseFloat(integerValue), Float.parseFloat(rs.getString("val")), 0);
assertTrue(rs.next());
assertEquals(Double.parseDouble(longValue), Double.parseDouble(rs.getString("val")), 0);
assertTrue(rs.next());
assertEquals(dateValue.toString(), rs.getString("val"));
assertTrue(rs.next());
assertEquals(timeValue.toString(), rs.getString("val"));
assertTrue(rs.next());
assertEquals(timestampValue.toString(), rs.getString("val"));
rs.close();
stmt.close();
}
public static void main(String[] args) {
junit.textui.TestRunner.run(SunTest.class);
}
}